﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Data;

namespace Librarian.Shared
{
    public class BookRules
    {
        static readonly string _codeBase = typeof(SearchEngine).Assembly.GetName().CodeBase;

        public static string GetConnectionString()
        {
#if PocketPC
            string path = Path.GetDirectoryName(_codeBase);
#else
            UriBuilder uri = new UriBuilder(_codeBase);
            string path = Path.GetDirectoryName(Uri.UnescapeDataString(uri.Path));
#endif

            string connectionString = string.Format("data source={0}", Path.Combine(path,
                "Data\\booksdata.s3db"));

            return connectionString;
        }

        public static long BookCount()
        {
            using (System.Data.SQLite.SQLiteConnection conn =
              new System.Data.SQLite.SQLiteConnection(BookRules.GetConnectionString()))
            {
                conn.Open();
                string query = "SELECT COUNT(*) FROM BOOKS";
                using (SQLiteCommand command = new SQLiteCommand(query, conn))
                {
                    object o = command.ExecuteScalar();
                    if (o != null && o.GetType() == typeof(long))
                    {
                        return (long)o;
                    }
                }
            }
            return 0;

        }

        public static void DeleteAllBooks()
        {
            using (System.Data.SQLite.SQLiteConnection conn =
             new System.Data.SQLite.SQLiteConnection(BookRules.GetConnectionString()))
            {
                conn.Open();

                using (SQLiteTransaction tran = conn.BeginTransaction())
                {

                    SQLiteCommand command = new SQLiteCommand("DELETE FROM KEYWORDSOFBOOK", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM BOOKS", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM THEMES", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM TYPES", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM AUTHORS", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM KEYWORDS", conn, tran);
                    command.ExecuteNonQuery();
                    command = new SQLiteCommand("DELETE FROM LOCATIONS", conn, tran);
                    command.ExecuteNonQuery();

                    tran.Commit();
                }
                
            }        

        }

        public static DataTable GetKeywords(long id)
        {
            DataTable t = new DataTable("KEYWORDS");
            t.PrimaryKey = new DataColumn[] { t.Columns.Add("ID", typeof(long)) };
            t.Columns.Add("NAME", typeof(string));
            using (System.Data.SQLite.SQLiteConnection conn =
             new System.Data.SQLite.SQLiteConnection(BookRules.GetConnectionString()))
            {
                conn.Open();

                using (SQLiteCommand command = new SQLiteCommand("SELECT ID, NAME FROM KEYWORDS JOIN KEYWORDSOFBOOK ON KEYWORDSOFBOOK.KEYWORDID=KEYWORDS.ID WHERE KEYWORDSOFBOOK.BOOKID=@1", conn))
                {
                    SQLiteParameter param= command.Parameters.Add("@1", DbType.Int64);
                    param.Value = id;
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            object[] o=new object[2];
                            reader.GetValues(o);
                            t.Rows.Add(o);
                        }
                    }

                }
            }
            return t;
        }
    }
}
